import pymysql
from PyQt6.QtCore import QObject
from utils.Logger import LogPrint

class MysqlDao(QObject):

    def __init__(self, host,port,user,password):

        LogPrint.debug(host,port,user,password)
        
        #初始化变量
        self.host=host
        self.port=port
        self.user=user
        self.password=password

        self.query_limit_count=100

        self.database=None
        #self.database='db_test'

    #读取所有库信息
    def get_all_databases(self):
         
        conn=self.get_conn()
        if conn:
            cursor = conn.cursor()
            cursor.execute("SHOW DATABASES")
            databases = cursor.fetchall()
            print("Available databases:",databases) 
            return databases
        
        return None


    #读取所有表信息
    def get_all_tables(self,db_name):

        self.database=db_name
         
        conn=self.get_conn()
        if conn:
            cursor = conn.cursor()
            cursor.execute("SHOW TABLES")
            tables = cursor.fetchall()
            print("Available tables:",tables) 
            return tables
        
        return None
    
    #读取所有表信息
    def get_table_data(self,db_name,table_name,page_no=0,contain_column_name=False):

        self.database=db_name
        try:
            conn=self.get_conn()
            if conn:
                cursor = conn.cursor()
                begin=page_no*self.query_limit_count
                sql=f"select * from {table_name} limit {begin},{self.query_limit_count}"
                print(sql)
                cursor.execute(sql)
                # 获取所有数据
                rows = cursor.fetchall()

                fields=None
                if contain_column_name:
                    # 获取所有字段名称
                    #fields = [field[0] for field in cursor.description]
                    cursor.execute(f'SHOW FULL COLUMNS FROM {table_name}')
                    fields=cursor.fetchall()
                    print("Fields:", fields)
                
                
                return fields,rows
        finally:
            # 关闭数据库连接
            conn.close()
        
        return None,None
        

    #获取数据库连接
    def get_conn(self):
            
            if self.database:

                conn=pymysql.connect(host=self.host,
                                    port=self.port,
                                    user=self.user,
                                    password=self.password,
                                    database=self.database)
            else :
                conn=pymysql.connect(host=self.host,
                                port=self.port,
                                user=self.user,
                                password=self.password)

            return conn